package com.example.booksystem.dao;


import com.example.booksystem.bean.Member;
import com.example.booksystem.utils.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class MemberDao {
    /**
     * 添加会员
     * @param name
     * @param pwd
     * @param typeId
     * @param balance
     * @param tel
     * @param idNumber
     * @return
     * @throws SQLException
     */
    public int add(String name,String pwd,long typeId,double balance,String tel,String idNumber) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        int result = 0;

        try {
            connection = DBUtil.getConnection();
            String sql="insert into member(`name`,pwd,typeId,balance,regdate,tel,idNumber) values(?,?,?,?,CURRENT_DATE,?,?)";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,name);
            preparedStatement.setString(2,pwd);
            preparedStatement.setLong(3,typeId);
            preparedStatement.setDouble(4,balance);
            preparedStatement.setString(5,tel);
            preparedStatement.setString(6,idNumber);
            result = preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(connection,preparedStatement,resultSet);
        }
        return result;

    }
    public int modify(long id,String name,String pwd,long typeId,double balance,String tel,String idNumber) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        int result = 0;

        try {
            connection = DBUtil.getConnection();
            String sql="update member set `name` = ?, pwd=?,typeId=?,balance = ?,tel=?,idNumber = ? where id=?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setLong(1,id);
            preparedStatement.setString(2,name);
            preparedStatement.setString(3,pwd);
            preparedStatement.setLong(4,typeId);
            preparedStatement.setDouble(5,balance);
            preparedStatement.setString(6,tel);
            preparedStatement.setString(7,idNumber);
            result = preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(connection,preparedStatement,resultSet);
        }
        return result;
    }
    public int remove(long id) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        int result = 0;

        try {
            connection = DBUtil.getConnection();
            String sql="delete from member where id=?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setLong(1,id);
            result = preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(connection,preparedStatement,resultSet);
        }
        return result;
    }

    /**
     * 会员的身份证号码
     * @param idNumber
     * @param amount
     * @return
     * @throws SQLException
     */
    public int modifyBalance(String idNumber,double amount){
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        int result = 0;

        try {
            connection = DBUtil.getConnection();
            String sql="update member set balance = balance + ? where idNumber = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setDouble(1,amount);
            preparedStatement.setString(2,idNumber);
            result = preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(connection,preparedStatement,resultSet);
        }
        return result;
    }


    public List<Member> getAll(){
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        ArrayList<Member> members = new ArrayList<>();

        try {
            connection = DBUtil.getConnection();
            String sql="select id,`name`,pwd,typeId,balance,regdate,tel,idNumber from  member";
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                Member member = new Member(resultSet.getLong(1),resultSet.getString(2),resultSet.getString(3),resultSet.getLong(4),resultSet.getDouble(5),resultSet.getDate(6),resultSet.getString(7),resultSet.getString(8));
                members.add(member);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(connection,preparedStatement,resultSet);
        }
        return members;
    }

    /**
     * 根据会员编号查会员信息
     * @param id
     * @return
     * @throws SQLException
     */
    public Member getById(long id){
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Member member = new Member();

        try {
            connection = DBUtil.getConnection();
            String sql="select id,`name`,pwd,typeId,balance,regdate,tel,idNumber from  member where id=?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setLong(1,id);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                member = new Member(resultSet.getLong(1),resultSet.getString(2),resultSet.getString(3),resultSet.getLong(4),resultSet.getDouble(5),resultSet.getDate(6),resultSet.getString(7),resultSet.getString(8));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(connection,preparedStatement,resultSet);
        }
        return member;
    }

    /**
     * 根据会员身份证查会员信息
     * @paramidNumber
     * @return
     * @throws SQLException
     */
    public Member getByIdNumber(String idNumber) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Member member = new Member();

        try {
            connection = DBUtil.getConnection();
            String sql="select id,`name`,pwd,typeId,balance,regdate,tel,idNumber from  member where idNumber=?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,idNumber);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                member = new Member(resultSet.getLong(1),resultSet.getString(2),resultSet.getString(3),resultSet.getLong(4),resultSet.getDouble(5),resultSet.getDate(6),resultSet.getString(7),resultSet.getString(8));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(connection,preparedStatement,resultSet);
        }
        return member;
    }


    /**
     * 判断会员编号是否存在Record中(作为外键 ）
     * @param id
     * @return
     */
    public boolean exits(long id) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        int count = 0;

        try {
            connection = DBUtil.getConnection();
            String sql="select count(id) from record where memberId = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setLong(1,id);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                count = resultSet.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(connection,preparedStatement,resultSet);
        }
        return count!=0;
    }

}
